<?php

namespace App\Console\Commands;

use App\AwardApplicationFormData;
use App\Project;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;

class ExportTalentAwardProjects extends Command
{
    protected $signature = 'export-talent-award-projects';

    public function handle()
    {
        $projects = Project::where('award_id', 2)
            ->whereNotNull('project_name')
            ->whereNotIn('status_id', ['B1', 'B1.2', 'B3'])
            ->orderBy('created_at')
            ->get();
        $count = count($projects);
        $records = [];
        foreach ($projects as $i => $project) {
            $num = $i + 1;
            $this->log("$num/$count #{$project->id} {$project->project_name}");
            $formDataRecord = AwardApplicationFormData::find($project->id);
            $formData = json_decode($formDataRecord->form_data, true);
            $dateOfBirthString = $formData['basic']['birth'];
            try {
                $dateOfBirth = new Carbon($dateOfBirthString);
                $age = $dateOfBirth->diffInYears();
            } catch (\Exception $e) {
                $this->log("Bad date of birth: $dateOfBirthString");
                if (preg_match('#\d{4}#', $dateOfBirthString, $m)) {
                    $dateOfBirthString = "{$m[0]}-01-01";
                    $this->log("Extracted date: $dateOfBirthString");
                    $dateOfBirth = new Carbon($dateOfBirthString);
                    $age = $dateOfBirth->diffInYears();
                } else {
                    $age = null;
                }
            }
            $stats = [
                '实用新型数量' => 0,
                '发明专利数量' => 0,
                '软件著作权数量' => 0,
                '科研项目数量' => 0,
                '参与重大项目数量' => 0,
            ];
            foreach ($formData['property'] as $propertyItem) {
                $type = $propertyItem['property_category'];
                if (str_contains($type, '发明')) {
                    $stats['发明专利数量'] += 1;
                }
                if (str_contains($type, '实用新型')) {
                    $stats['实用新型数量'] += 1;
                }
                if (str_contains($type, '软件')) {
                    $stats['软件著作权数量'] += 1;
                }
            }
            $rdProjectFundingSum = 0;
            foreach ($formData['scientific'] as $projectItem) {
                $fundingValue = $projectItem['expenditure'];
                try {
                    $rdProjectFundingSum += $fundingValue;
                } catch (\Exception $e) {
                    $this->log("Bad project funding value: $fundingValue");
                    if (preg_match('#\d+#', $fundingValue, $m)) {
                        $fundingValue = $m[0];
                        $this->log("Extracted funding value: $fundingValue");
                        $rdProjectFundingSum += $fundingValue;
                    }
                }
            }
            $record = [
                '序号' => $num,
                '人选姓名' => $project->project_name,
                '年龄' => $age,
                '工作单位' => $formData['basic']['company'],
                '行政职务_专业职务' => $formData['basic']['duty'] . "\n" . $formData['basic']['professional'],
                '奖励或荣誉名称' => implode("\n", array_map(function ($item) use ($project) {
                    if (preg_match('#\d{4}#', $item['date'], $m)) {
                        $year = $m[0];
                    } else {
                        $year = '未知年份';
                    }
                    return "$year {$item['award_name']}";
                }, $formData['award'])),
                '论文_著作名称' => implode("\n", array_map(function ($item) {
                    return $item['paper_name'];
                }, $formData['paper'])),
                '实用新型数量' => $stats['实用新型数量'],
                '发明专利数量' => $stats['发明专利数量'],
                '软件著作权数量' => $stats['软件著作权数量'],
                '科研项目数量' => count($formData['scientific']),
                '研发经费总额' => (int)$rdProjectFundingSum,
                '参与重大项目数量' => count($formData['engineering']),
                '参与重大项目经济效益总额' => implode("\n", array_map(function ($item) {
                    return $item['economic'];
                }, $formData['engineering'])),
                '成果转化及推广' => $formData['situation'],
            ];
            $records[] = $record;
        }
        DB::statement('truncate _科技人物奖候选人情况汇总');
        DB::table('_科技人物奖候选人情况汇总')->insert($records);
    }
}
